﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using HtmlAgilityPack;
using MySql.Data;
using MySql.Data.MySqlClient;


namespace wmdata
{
    public partial class trident : mysqlconn
    {
        public trident(string record, string database)
        {
            wmdatabse = database;
            record_id = record;
            this.Load += new EventHandler(storepage_Load);
        }

        private void storepage_Load(object sender, EventArgs e)
        {            
            wmstore.DocumentCompleted += new WebBrowserDocumentCompletedEventHandler(wmstore_DocumentCompleted);
            wmstore.AllowNavigation = true;            
            string url = "https://wmt.quickbase.com/db/bfngn7tvg?a=dr&rid=" + record_id + "&rl=wm3";                   
            wmstore.Navigate(url);
        }

        void wmstore_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
        {
            connect(conn);
            //conn.Open();
            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(wmstore.DocumentStream);
            string[] tables = {"//table[@id='sect_s1']//tr//td", "//table[@id='sect_s74']//tr//td", "//table[@id='sect_s4']//tr//td", "//table[@id='sect_s33']//tr//td", "//table[@id='sect_s34']//tr//td" +
                                  "//table[@id='sect_s97']//tr//td", "//table[@id='sect_s90']//tr//td", "//table[@id='sect_s5']//tr//td", "//table[@id='sect_s16']//tr//td", "//table[@id='sect_s14']//tr//td" +
                                    "//table[@id='sect_s43']//tr//td"};

            string strsel = "SELECT store_information.id, store_information.number, store_information.wm_owned, store_type.name, address.address, address.longitude, address.latitude, street_intersection.name, " +
                                "city.name, zip.zipcode, county.name, division.name, reqion.name, state.abbreviation, store_information.timestamp " +
                                "FROM store_information " +
                                "JOIN store_type " +
                                "ON (store_information.store_type_id = store_type.id) " +
                                "JOIN address " +
                                "ON (store_information.address_id = address.id) " +
                                "JOIN street_intersection " +
                                "ON (store_information.street_intersection_id = street_intersection.id) " +
                                "JOIN city " +
                                "ON (store_information.city_id = city.id) " +
                                "JOIN zip " +
                                "ON (store_information.zip_id = zip.id) " +
                                "JOIN county " +
                                "ON (store_information.county_id = county.id) " +
                                "JOIN division " +
                                "ON (store_information.division_id = division.id) " +
                                "JOIN region " +
                                "ON (store_information.region_id = region.id) " +
                                "JOIN state " +
                                "ON (store_information.state_id = state.id) ";

            string strinsertpre = "INSERT INTO store_information (";

            string strinsertmid = " ) SELECT ";

            string strinsertpost = "FROM store_information " +
                                "JOIN store_type " +
                                "ON (store_information.store_type_id = store_type.id) " +
                                "JOIN address " +
                                "ON (store_information.address_id = address.id) " +
                                "JOIN street_intersection " +
                                "ON (store_information.street_intersection_id = street_intersection.id) " +
                                "JOIN city " +
                                "ON (store_information.city_id = city.id) " +
                                "JOIN zip " +
                                "ON (store_information.zip_id = zip.id) " +
                                "JOIN county " +
                                "ON (store_information.county_id = county.id) " +
                                "JOIN division " +
                                "ON (store_information.division_id = division.id) " +
                                "JOIN region " +
                                "ON (store_information.region_id = region.id) " +
                                "JOIN state " +
                                "ON (store_information.state_id = state.id) ";

            string prjsel = "SELECT project_information.id, project_type.name, store_type.name, development_type.name, description_of_type.name, program_year.projected_year, program_year.actual_year, program_category.name, " +
                                "prototype.name, prototype.class, prototype.entrance, project_status.name, project_phase.name, project_information.sequence_number, project_information.confidential, " +
                                "project_information.combo, project_information.manage_sites_different, project_information.sap, project_information.store_square_footage, project_information.increase_square_footage, " +
                                "project_information.prj_total_square_footage, project_information.act_total_square_footage, project_information.friction_carts, project_information.cart_contain, project_information.perm_carts, " +
                                "project_information.liquor, project_information.roadsign, project_information.wastewater, project_information.tle_appr, project_information.garden_appr, project_information.seasonal_appr, " +
                                "project_information.pharmacy_appr, project_information.gas_appr, project_information.led_parking_light, project_information.energy_proj, project_information.qtr_move_pct, project_information.yr_move_pct, " +
                                "project_information.proj_drop_pct, project_information.offsite_on_sched, project_information.traff_on_sched, project_information.onsite_on_sched, project_information.bldg_on_sched, project_information.const_risk_rating, " +
                                "project_information.cost_to_accel, project_information.ref_on_sched, project_information.constr_dur, project_information.proto_dur, project_information.early_fixt_set, project_information.timestamp " +
                                "FROM project_information " +
                                "JOIN project_type " +
                                "ON (project_information.project_type_id = project_type.id) " +
                                "JOIN store_type " +
                                "ON (project_information.store_type_id = store_type.id) " +
                                "JOIN development_type " +
                                "ON (project_information.development_type.id = development_type.id) " +
                                "JOIN description_of_type " +
                                "ON (project_information.description_of_type_id = description_of_type.id) " +
                                "JOIN program_year " +
                                "ON (project_information.program_year_id = program_year.id) " +
                                "JOIN program_category " +
                                "ON (project_information.program_category.id = program_category.id) " +
                                "JOIN prototype " +
                                "ON (project_information.prototype.id = prototype.id) " +
                                "JOIN project_status " +
                                "ON (project_information.project_status.id = project_status.id) " +
                                "JOIN project_phase " +
                                "ON (project_information.project_phase.id = project_phase.id) ";

            string prjcontsel = "SELECT project_contacts.id, job_roles.name, contacts.first_name, contacts.last_name, store_information.locator, project_information.sequence_number, project_contacts.timestamp " +
                                    "FROM project_contacts " +
                                    "JOIN job_roles " +
                                    "ON (project_contacts.job_role_id = job_role.id) " +
                                    "JOIN contacts " +
                                    "ON (project_contacts.contact_id = contact.id) " +
                                    "JOIN store_information " +
                                    "ON (project_contacts.store_information.id = store_information.id) " +
                                    "JOIN project_information " +
                                    "ON (project_contacts.project_information_id = project_information.id) ";

            string tridentsel = "SELECT trident_dates.id, project_information.sequence_number, trident_milestones.name, trident_dates.projected_start, trident_dates.projected_finish, trident_dates.actual_start, " +
                                    "trident_dates.actual_finish, trident_dates.duration, trident_dates.projected_start_override, trident_dates.not_applicable, trident_dates.timestamp " +
                                    "FROM trident_dates " +
                                    "JOIN project_information " +
                                    "ON (trident_dates.project_information.id = project_information.id) " +
                                    "JOIN trident_milestones " +
                                    "ON (trident_dates.trident_milestones.id = trident_milestones.id) ";


            string prjcommsel = "SELECT project_comments.id, project_comments.project_risk_comments_id, project_comments.cec_comments_id, project_comments.aor_comments_id, project_comments.constr_risk_comments_id," +
                                    " project_comments.project_delay_comments_id " +
                                    "FROM project_comments " +
                                    "JOIN comments " +
                                    "ON (project_comments.project_risk_comments_id = comments_id) AND (project_comments.cec_comments_id = comments_id) AND (project_comments.aor_comments_id = comments_id) AND " + 
                                    "(project_comments.constr_risk_comments_id = comments_id) AND (project_comments.project_delay_comments_id = comments_id) ";


            string compsel = "SELECT companies.id, company.name, address.address, address.suite, address.longitude, adress.latitude, city.name, state.abbreviation, zip.zipcode, companies.main_phone, " +
                                    "companies.fax, categories.category, companies.timestamp " +
                                    "FROM companies " +
                                    "JOIN company " +
                                    "ON (companies.company_id = company.id) " +
                                    "JOIN address " +
                                    "ON (companies.address_id = address.id) " +
                                    "JOIN city " +
                                    "ON (companies.city_id = city.id) " +
                                    "JOIN state " +
                                    "ON (companies.state_id = state.id) " +
                                    "JOIN zip " +
                                    "ON (companies.zip_id = zip.id) ";


            //+
            //"WHERE (store.locator LIKE storevalue) " +
            //"AND (type.name LIKE typevalue) " +
            //"AND (ad.address LIKE advalue) " +
            //"AND (ad.longitude LIKE longvalue) " +
            //"AND (ad.latitude LIKE latvalue) " +
            //"AND (street.name LIKE streetvalue) " +
            //"AND (city.name LIKE cityvalue) " +
            //"AND (zip.zipcode LIKE zipvalue) " +
            //"AND (county.name LIKE ctyvalue) " +
            //"AND (division.name LIKE divvalue) " +
            //"AND (region.name LIKE regvalue) " +
            //"AND (state.name LIKE statevalue) " +
            //"AND (store.timestamp = (Select max(timestamp) from rha.store_information))";

            StringBuilder sbcomp = new StringBuilder(compsel);
            StringBuilder sbstr = new StringBuilder(strsel);
            StringBuilder sbstrinsert = new StringBuilder(strinsertpre);
            StringBuilder sbstrmid = new StringBuilder(strinsertmid);
            StringBuilder sbprj = new StringBuilder(prjsel);
            StringBuilder sbprjcont = new StringBuilder(prjcontsel);
            StringBuilder sbtrident = new StringBuilder(tridentsel);

            for (int t = 0; t < tables.Length; t++)
            {
                HtmlNodeCollection cells = doc.DocumentNode.SelectNodes(tables[t]);                

                for (int c = 0; c < cells.Count; c++)
                {
                    HtmlNodeCollection nodes = cells[c].ChildNodes;
                    relationtable = null;
                    table = null;
                    column = null;
                    label = null;

                    for (int n = 0; n < nodes.Count; n++)
                    {
                        switch (nodes[n].Name)
                        {
                            case "label":
                                label = nodes[n].InnerText.ToString();
                                label = word(label);
                                break;
                                
                            case "span":
                                string span = nodes[n].InnerText.ToString();
                                span = word(span);

                                switch (span)
                                {
                                    case "Not for Public Release":
                                        break;
                                    case "Confidential":
                                        break;
                                    case "MSD - Managing Sites Differently":
                                        table = "project_information";
                                        break;
                                }
                                break;
                            case "div":
                                value = nodes[n].InnerText.ToString();
                                word(value);
                                
                                switch (label)
                                {                                    
                                    case "Store":
                                        if (!String.IsNullOrWhiteSpace(value)) sbstr.Append(prefix(sbstr) + " store_information.number = " + value);
                                        else mailman.sendmail("Store Number Missing", "Please verify why there is no store number.");
                                        break;
                                    case "Seq #":
                                        if (!String.IsNullOrWhiteSpace(value)) sbprj.Append(prefix(sbprj) + " project_information.sequence_number = " + value);
                                        else mailman.sendmail("Sequence NUmber Missing", "Please verify why there is no sequence number.");
                                        break;
                                    case "Project Status":
                                        recordid = CheckRecordExists("project_status", "name", value);
                                        if (recordid == 0)
                                        {
                                            AddRecord("project_status", "name", value);
                                            recordid = CheckRecordExists("project_status", "name", value);
                                            sbprj.Append(prefix(sbprj) + " project_status.id = " + recordid + ")");
                                        }
                                        else
                                        { sbprj.Append(prefix(sbprj) + " project_status.id = " + recordid + ")"); }
                                        break;
                                    case "Project Phase":
                                        recordid = CheckRecordExists("project_phase", "name", value);
                                        if (CheckRecordExists("project_phase", "name", value) == 0)
                                        {
                                            AddRecord("project_phase", "name", value);
                                            recordid = CheckRecordExists("project_phase", "name", value);
                                            sbprj.Append(prefix(sbprj) + " project_phase.id = " + recordid + ")");
                                        }
                                        else
                                        { sbprj.Append(prefix(sbprj) + " project_phase.id = " + recordid + ")"); }
                                        break;
                                    case "Program Year Projected":
                                        progyr = new string[2, 2];
                                        progyr[0, 0] = "projected_year";
                                        progyr[1, 0] = value;       
                                        break;
                                    case "Program Year Actual":
                                        progyr[0, 1] = "actual_year";
                                        progyr[1, 1] = value;
                                        long pyid = 0;
                                        if (CheckRecordExistsProgYr("program_year", progyr) == 0)
                                        { pyid = AddRecordProgYr("program_year", progyr); }
                                        sbprj.Append(prefix(sbprj) + " program_year.id = " + pyid + ")");
                                        break;
                                    case "Project Type":
                                        recordid = CheckRecordExists("project_type", "name", value);
                                        if (recordid == 0)
                                        {
                                            AddRecord("project_type", "name", value);
                                            recordid = CheckRecordExists("project_type", "name", value);
                                            sbprj.Append(prefix(sbprj) + "project_type.id = " + recordid + ")");
                                        }
                                        else
                                        { sbprj.Append(prefix(sbprj) + "project_type.id = " + recordid + ")"); } 
                                        break;
                                    case "Store Type":
                                        recordid = CheckRecordExists("store_type", "name", value);
                                        if (recordid == 0)
                                        {
                                            AddRecord("store_type", "name", value);
                                            recordid = CheckRecordExists("store_type", "name", value);
                                            sbstr.Append(prefix(sbstr) + "store_type.id = " + recordid + ")");
                                        }
                                        else { sbstr.Append(prefix(sbstr) + "store_type.id = " + recordid + ")"); }
                                        break;
                                    case "Region":
                                        recordid = CheckRecordExists("region", "name", value);
                                        if (recordid == 0)
                                        {
                                            AddRecord("region", "name", value);
                                            recordid = CheckRecordExists("region", "name", value);
                                            sbstr.Append(prefix(sbstr) + "region.id = " + recordid + ")");
                                        }
                                        else { sbstr.Append(prefix(sbstr) + "region.id = " + recordid + ")"); }
                                        break;
                                    case "GBU Division":
                                        recordid = CheckRecordExists("division", "name", value);
                                        if (recordid == 0)
                                        {
                                            AddRecord("division", "name", value);
                                            recordid = CheckRecordExists("division", "name", value);
                                            sbstr.Append(prefix(sbstr) + "division.id = " + recordid + ")");
                                        }
                                        else { sbstr.Append(prefix(sbstr) + "division.id = " + recordid + ")"); }
                                        break;
                                    case "Combo Sams/Walmart?":
                                        {
                                            if (!String.IsNullOrEmpty(value))
                                                if (value == "No")
                                                { sbprj.Append(prefix(sbprj) + "project_information.combo = 0"); }
                                                else if (value == "Yes")
                                                { sbprj.Append(prefix(sbprj) + "project_information.combo = 1"); }
                                        }
                                        break;
                                    case "Dev Type":
                                        recordid = CheckRecordExists("development_type", "name", value);
                                        if (recordid == 0)
                                        {
                                            AddRecord("development_type", "name", value);
                                            recordid = CheckRecordExists("development_type", "name", value);
                                            sbprj.Append(prefix(sbprj) + "development_type.id = " + recordid + ")");
                                        }
                                        else { sbprj.Append(prefix(sbprj) + "development_type.id = " + recordid + ")"); }
                                        break;
                                    case "Developer Parent Company Name":
                                        recordid = CheckRecordExists("company", "name", value);
                                        if (recordid == 0)
                                        {
                                            AddRecord("company", "name", value);
                                            recordid = CheckRecordExists("company", "name", value);
                                            sbcomp.Append(prefix(sbcomp) + "company.id = " + recordid + ")");
                                        }
                                        else { sbcomp.Append(prefix(sbcomp) + "company.id = " + recordid + ")"); }
                                        break;
                                    case "Developer LLC Name":
                                        recordid = CheckRecordExists("company", "name", value);
                                        if (recordid == 0)
                                        {
                                            AddRecord("company", "name", value);
                                            recordid = CheckRecordExists("company", "name", value);
                                            sbcomp.Append(prefix(sbcomp) + "company.id = " + recordid + ")");
                                        }
                                        else { sbcomp.Append(prefix(sbcomp) + "company.id = " + recordid + ")"); }
                                        break;
                                    case "Developer Primary Contact Name":
                                        {
                                            name = value.Split(new Char[] {' '});

                                        }
                                        break;
                                    case "Developer Primary Contact Telephone Number":
                                        break;
                                    case "Developer Primary Contact Email Address":
                                        break;                                    
                                    case "SAP Project Definition":
                                        relationtable = "project_information";
                                        column = "sap";
                                        break;
                                    case "Stores Location/Intersection":
                                        relationtable = "store_information";
                                        table = "street_intersection";
                                        column = "name";
                                        break;
                                    case "Address":
                                        relationtable = "store_information";
                                        table = "address";
                                        addr = new string[2, 4];
                                        addr[0, 0] = "address";
                                        break;
                                    case "City":
                                        relationtable = "store_information";
                                        table = "city";
                                        column = "name";
                                        break;
                                    case "State":
                                        relationtable = "store_information";
                                        table = "state";
                                        column = "abbreviation";
                                        break;
                                    case "Postal Code":
                                        relationtable = "store_information";
                                        table = "zip";
                                        column = "zipcode";
                                        break;
                                    case "Latitude":
                                        relationtable = "store_information";
                                        table = "address";
                                        addr[0, 3] = "latitude"; 
                                        break;
                                    case "Longitude":
                                        relationtable = "store_information";
                                        table = "address";
                                        addr[0, 1] = "longitude";
                                        addrbool = true;
                                        break;
                                    case "Property WM Owned":
                                        relationtable = "store_information";
                                        column = "wm_owned";
                                        break;                                        
                                    case "Total Store Area Prj (Sq Ft)":
                                        relationtable = "project_information";
                                        column = "prj_total_square_footage";
                                        break;
                                    case "Total Store Area Act (Sq Ft)":
                                        relationtable = "project_information";
                                        column = "act_total_square_footage";
                                        break;
                                    case "Proto":
                                        relationtable = "project_information";
                                        table = "prototype";
                                        proto_col[0] = "name";
                                        break;
                                    case "Prototype Class":
                                        table = "prototype";
                                        proto_col[1] = "class";
                                        break;
                                    case "Prototype of Entrance":
                                        table = "prototype";
                                        proto_col[2] = "entrance";
                                        break;
                                    case "Need Friction Carts":
                                        relationtable = "project_information";
                                        column = "friction_carts";
                                        break;
                                    case "Cart Containment":
                                        relationtable = "project_information";
                                        column = "cart_contain";
                                        break;
                                    case "Permanent Cart Corrals Required":
                                        relationtable = "project_information";
                                        column = "perm_carts";
                                        break;
                                    case "Liquor":
                                        relationtable = "project_information";
                                        column = "liquor";
                                        break;
                                    case "Roadside Sign":
                                        relationtable = "project_information";
                                        column = "roadsign";
                                        break;
                                    case "Wastewater Treatment Type":
                                        relationtable = "project_information";
                                        column = "wastewater";
                                        break;
                                    case "OTB ID Date":
                                        labeltable = "trident_milestones";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        table = "trident_dates";
                                        break;
                                    case "Final ID Date":
                                        labeltable = "trident_milestones";
                                        labelcolumn = "name";                                                
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        table = "trident_dates";
                                        break;
                                    case "Section Count/Final Power Plan Date":
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        table = "trident_dates";
                                        break;
                                    case "Received Merchandise Plan":
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        table = "trident_dates";
                                        break;
                                    case "Energy Dept Project":
                                        relationtable = "project_information";
                                        column = "energy_proj";
                                        break;
                                    case "TLE Approved by REC":
                                        relationtable = "project_information";
                                        column = "tle_appr";
                                        break;
                                    case "Garden Center Approved by REC":
                                        relationtable = "project_information";
                                        column = "garden_appr";
                                        break;
                                    case "Seasonal Area Approved by REC":
                                        relationtable = "project_information";
                                        column = "seasonal_appr";
                                        break;
                                    case "Pharmacy Approved by REC":
                                        relationtable = "project_information";
                                        column = "pharmacy_appr";
                                        break;
                                    case "Gas Station Approved by REC":
                                        relationtable = "project_information";
                                        column = "gas_appr"; 
                                        break;
                                    case "LED parking lot lighting":
                                        relationtable = "project_information";
                                        column = "led_parking_light";
                                        break;
                                    case "Probability of Quarter Move (0, 25, 50, 75, 100%)":
                                        relationtable = "project_information";
                                        column = "qtr_move_pct";
                                        break;
                                    case "Probability of Year Move (0, 25, 50, 75, 100%)":
                                        relationtable = "project_information";
                                        column = "yr_move_pct";
                                        break;
                                    case "Probability of Project Dropping (0, 25, 50, 75, 100%)":
                                        relationtable = "project_information";
                                        column = "proj_drop_pct";
                                        break;
                                    case "Offsite on Schedule (Yes/No)":
                                        relationtable = "project_information";
                                        column = "offsite_on_sch";
                                        break;
                                    case "Traffic Signal On Schedule (Yes/No)":
                                        relationtable = "project_information";
                                        column = "traff_on_sch";
                                        break;
                                    case "Onsite on Schedule (Yes/No)":
                                        relationtable = "project_information";
                                        column = "onsite_on_sch";
                                        break;
                                    case "Building on Schedule (Yes/No)":
                                        relationtable = "project_information";
                                        column = "bldg_on_sch";
                                        break;
                                    case "Construction Completion Risk Rating":
                                        relationtable = "project_information";
                                        column = "const_risk_rating";
                                        break;
                                    case "Comments (Schedule/Risk/Duration)":
                                        labeltable = "comment_type";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "project_comments";
                                        table = "comment";
                                        column = "schedule_risk_duration_comm_id";
                                        break;
                                    case "Cost To Accelerate":
                                        relationtable = "project_information";
                                        column = "cost_to_accel";
                                        break;
                                    case "Refrigeration On Schedule":
                                        relationtable = "project_information";
                                        column = "refr_on_sched";
                                        break;
                                    case "CEC Comments":
                                        labeltable = "comment_type";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "comments";
                                        break;
                                    case "AOR Comments":
                                        labeltable = "comment_type";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "comments";
                                        break;
                                    case "Construction at Risk Comments":
                                        labeltable = "comment_type";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "comments";
                                        break;
                                    case "Project Delay Comments":
                                        labeltable = "comment_type";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "comments";
                                        break;
                                    case "Developer Pad Date - PF":
                                        label = "Developer Pad";
                                        labeltable = "trident_milestones";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "trident_dates";
                                        tridentcol[0] = "projected_finish";
                                        break;
                                    case "Developer Pad Date - AF":
                                        label = "Developer Pad";
                                        labeltable = "trident_milestones";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "trident_dates";                                                
                                        tridentcol[1] = "actual_finish";
                                        break;
                                    case "Conduct Pre-Bid Meeting":
                                        labeltable = "trident_milestones";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "trident_dates";                                                
                                        tridentcol[0] = "actual_finish";
                                        tridentcol[1] = null;
                                        break;
                                    case "Developer Construction Start Date - PF":
                                        label = "Developer Construction";
                                        labeltable = "trident_milestones";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "trident_dates";
                                        tridentcol[0] = "projected_start";
                                        break;                                                
                                    case "Developer Construction Complete - AF":
                                        label = "Developer Construction";
                                        labeltable = "trident_milestones";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "trident_dates";                                                
                                        tridentcol[1] = "actual_finish";
                                        break;                                            
                                    case "Developer Construction Complete PF":
                                        label = "Developer Construction";
                                        labeltable = "trident_milestones";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "trident_dates";                                                
                                        tridentcol[0] = "projected_finish";
                                        break;
                                    case "Developer Construction Start Date - AF":
                                        label = "Developer Construction";
                                        labeltable = "trident_milestones";
                                        labelcolumn = "name";
                                        CheckLabelExists(label, labeltable, labelcolumn);
                                        relationtable = "trident_dates";                                                
                                        tridentcol[0] = "actual_start";
                                        break;
                                    case "CM Estimated Construction Duration (OTB to Poss) days":                                                
                                        relationtable = "project_information";
                                        tridentcol[0] = "constr_dur";
                                        break;
                                    case "Proto Duration":
                                        relationtable = "project_information";
                                        tridentcol[0] = "proto_dur";
                                        break;
                                    case "Superintendent Name":
                                        break;
                                    case "Superintendent Phone":
                                        break;
                                    case "Early Fixture Set":
                                        relationtable = "project_information";
                                        tridentcol[0] = "early_fix_set";
                                        break;                                       
                                    case "Final Doc Closeout Appvd - AF":
                                        break;
                                    case "Final Doc Closeout Recvd - AF":
                                        break;
                                    case "Date Temporary Occupancy Issued":
                                        break;
                                    case "Date Temporary Occupancy Expires":
                                        break;
                                    case "Date Final C of O Issued by Gov. Entity":
                                        break;
                                    case "Certificate of Occupancy Comments":
                                        break;
                                    case "Close Out Books Comments":
                                        break;
                                    case "Estimator":
                                        break;
                                        
                                    }
                                    break;
                                    
                                
                                //{
                                //    switch (table)
                                //    {
                                //        case "project_status":
                                //        case "project_phase":                                        
                                //            exists = CheckRecordExists(table, column, value);
                                //            if (exists == false) AddRecord(table, column, value);
                                //            break;
                                //        case "program_year":
                                //            if (pybool == false) progyr[1,0] = value;
                                //            else if (pybool == true)
                                //            {
                                //                progyr[1, 1] = value;
                                //                exists = CheckRecordExistsProgYr(table, progyr);
                                //                if (exists == false) AddRecordProgYr(table, progyr);
                                //            }                                           
                                //            break;                                        
                                //    }

                                //    switch (relationtable)
                                //    {
                                //        case "store_information":
                                //            if (!sbstr.ToString().Contains("WHERE"))
                                //            {
                                //                switch (label)
                                //                {
                                //                    case "Store":
                                //                    case "Property WM Owned":
                                //                    case "Latitude":
                                //                    case "Longitude":
                                //                        sbstr.Append("WHERE (" + relationtable + "." + column + " = '" + value + "') ");
                                //                        break;
                                //                    case "Store Type":
                                //                    case "Region":
                                //                    case "GBU Division":
                                //                    case "Dev Type":
                                //                    case "Stores Location/Intersection":
                                //                    case "Address":
                                //                    case "City":
                                //                    case "State":
                                //                    case "Postal Code":
                                //                        sbstr.Append("WHERE (" + table + "." + column + " = '" + value + "') ");
                                //                        break;
                                //                }
                                //            }
                                //            else
                                //            {
                                //                switch (label)
                                //                {
                                //                    case "Store":
                                //                    case "Property WM Owned":
                                //                    case "Latitude":
                                //                    case "Longitude":
                                //                        sbstr.Append("AND (" + relationtable + "." + column + " = '" + value + "') "); 
                                //                        break;
                                //                    case "Store Type":
                                //                    case "Region":
                                //                    case "GBU Division":
                                //                    case "Dev Type":
                                //                    case "Stores Location/Intersection":
                                //                    case "Address":
                                //                    case "City":
                                //                    case "State":
                                //                    case "Postal Code":
                                //                        sbstr.Append("AND (" + table + "." + column + " = '" + value + "') "); 
                                //                        break;
                                //                }                                                
                                //            }
                                //            break;
                                //        case "project_information":
                                //            if (!sbprj.ToString().Contains("WHERE"))
                                //            {
                                //                switch (label)
                                //                {
                                //                    case "Seq #":
                                //                    case "Combo Sams/Walmart?":
                                //                    case "SAP Project Definition":
                                //                    case "Total Store Area Prj (Sq Ft)":
                                //                    case "Total Store Area Act (Sq Ft)":
                                //                    case "Need Friction Carts":
                                //                    case "Cart Containment":
                                //                    case "Permanent Cart Corrals Required":
                                //                    case "Liquor":
                                //                    case "Roadside Sign":
                                //                    case "Wastewater Treatment Type":
                                //                    case "TLE Approved by REC":
                                //                    case "Garden Center Approved by REC":
                                //                    case "Seasonal Area Approved by REC":
                                //                    case "Pharmacy Approved by REC":
                                //                    case "Gas Station Approved by REC":
                                //                    case "LED parking lot lighting":
                                //                        sbprj.Append("WHERE (" + relationtable + "." + column + " = '" + value + "') ");
                                //                        break;
                                //                    case "Project Status":
                                //                    case "Project Phase":                                                   
                                //                    case "Project Type":
                                //                    case "Store Type":
                                //                    case "Proto":
                                //                    case "Prototype Class":
                                //                    case "Prototype of Entrance":
                                //                        sbprj.Append("WHERE (" + table + "." + column + " = '" + value + "') ");
                                //                        break;
                                //                }                                         
                                //            }
                                //            else
                                //            {
                                //                switch (label)
                                //                {
                                //                    case "Seq #":
                                //                    case "Combo Sams/Walmart?":
                                //                    case "SAP Project Definition":
                                //                    case "Total Store Area Prj (Sq Ft)":
                                //                    case "Total Store Area Act (Sq Ft)":
                                //                    case "Need Friction Carts":
                                //                    case "Cart Containment":
                                //                    case "Permanent Cart Corrals Required":
                                //                    case "Liquor":
                                //                    case "Roadside Sign":
                                //                    case "Wastewater Treatment Type":
                                //                    case "TLE Approved by REC":
                                //                    case "Garden Center Approved by REC":
                                //                    case "Seasonal Area Approved by REC":
                                //                    case "Pharmacy Approved by REC":
                                //                    case "Gas Station Approved by REC":
                                //                    case "LED parking lot lighting":
                                //                        sbprj.Append("AND (" + relationtable + "." + column + " = '" + value + "') "); 
                                //                        break;
                                //                    case "Project Status":
                                //                    case "Project Phase":                                                    
                                //                    case "Project Type":
                                //                    case "Store Type":
                                //                    case "Proto":
                                //                    case "Prototype Class":
                                //                        sbprj.Append("AND (" + table + "." + column + " = '" + value + "') ");
                                //                        break;
                                //                }                                                
                                //            }
                                //            break;
                                //        case "comments":

                                //            break;
                                //    }
                                //}
                                //break;
                                //else
                                //{ continue; }                                
                                //break;
                        }
                    }
                }
            }            
            wmdata.tridentpage.Close();
            wmdata.tridentpage.Dispose();
            wmstore.Dispose();
        }

        public static string word(string name)
        {
            name = name.Replace("\r\n", "").Trim();
            name = Regex.Replace(name, @"\s+", " ");            
            return name;
        }

        public string prefix(StringBuilder sb)
        {
            if (!sb.ToString().Contains("WHERE")) return "WHERE (";
            else return " ) AND (";
        }

        string[] name;
        int recordid;
        MySqlConnection conn = new MySqlConnection();
        string[] tridentcol;
        string[] tridentval;      
        string labelcolumn;
        string labeltable;
        string[] proto_col;
        string[] proto_val;
        bool addrbool = false;
        string[,] addr;   
        string[] contact_col;
        string[] contact_val;        
        string[,] progyr;        
        bool exists;
        string label;
        string relationtable;
        string sbcolumn;
        string column;
        string valuename = null;
        string table = null;
        string value = null;
        public MySqlCommand command = new MySqlCommand();
        DataTable[] dt = null;
        string[] tables;
        public string wmdatabse;
        DataSet wmds;
        string record_id;
        WebBrowser wmstore = new WebBrowser();
    }
}
